package cn.pconline.autoclub.service;

import cn.pconline.autoclub.entity.ClubAddress;
import cn.pconline.autoclub.entity.ClubAwardRecord;
import cn.pconline.autoclub.util.DateUtils;
import cn.pconline.autoclub.util.Pager;
import cn.pconline.autoclub.util.club.ClubUpgradeRecord;
import com.alibaba.fastjson.JSONObject;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.gelivable.dao.GeliDao;
import org.gelivable.dao.SqlBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

public class ClubAwardRecordService {

    @Autowired
    private GeliDao geliDao;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ClubService clubService;
    @Autowired
    private ThemeService themeService;
    @Autowired
    private PagerService pagerService;

    public ClubAwardRecord getById(long id) {
        try {
            return geliDao.find(ClubAwardRecord.class, id);
        } catch (DataAccessException e) {
            return null;
        }
    }

    @Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    public long create(ClubAwardRecord o) {
        return geliDao.create(o);
    }

    @Transactional(propagation = Propagation.REQUIRED, readOnly = false)
    public long update(ClubAwardRecord o) {
        return geliDao.update(o);
    }

    /**
     * 升级车友会时自动插入领奖记录，地址默认车友会地址
     *
     * @param req
     * @param resp
     * @return
     */
    public ClubAwardRecord autoInsert(long clubId, long levelId) {
        ClubAwardRecord car = this.findByClubId(clubId, levelId);
        ClubAddress address = clubService.getAddress(clubId);
        String name = "";
        String addressDesc = "";
        String postCode = "";
        String phone = "";
        if (address != null) {
            name = address.getName();
            addressDesc = address.getProvinceName() + address.getCityName() + address.getAddress();
            postCode = address.getPostcode();
            phone = address.getPhone();
        }
        if (car == null) {
            ClubAwardRecord clubAwardRecord = new ClubAwardRecord();
            clubAwardRecord.setClubId(clubId);
            clubAwardRecord.setLevelId(levelId);
            clubAwardRecord.setUpdateTime(DateUtils.getNow());
            clubAwardRecord.setName(name);
            clubAwardRecord.setAddress(addressDesc);
            clubAwardRecord.setPostalCode(postCode);
            clubAwardRecord.setPhone(phone);

            long id = create(clubAwardRecord);
            clubAwardRecord.setClubAwardRecordId(id);
            return clubAwardRecord;
        }
        return car;
    }

    public ClubAwardRecord findByClubId(long clubId, long levelId) {
        String sql = "SELECT * from ac_club_award_record where club_id=? and level_id=?";
        return this.geliDao.findFirst(ClubAwardRecord.class, sql, clubId, levelId);
    }

    public JSONObject findByClubIdToJSON(long clubId, long levelId) {
        ClubAwardRecord c = findByClubId(clubId, levelId);
        return getClubAwardRecordJSON(c);
    }

    private JSONObject getClubAwardRecordJSON(ClubAwardRecord c) {
        JSONObject json = new JSONObject();
        if (c != null) {
            json.put("address", c.getAddress());
            json.put("clubAwardRecordId", c.getClubAwardRecordId());
            json.put("clubId", c.getClubId());
            json.put("clubName", c.getClubName() == null ? "" : c.getClubName());
            json.put("levelId", c.getLevelId());
            json.put("address", c.getAddress() == null ? "" : c.getAddress());
            json.put("name", c.getName() == null ? "" : c.getName());
            json.put("phone", c.getPhone() == null ? "" : c.getPhone());
            json.put("postalcode", c.getPostalCode() == null ? "" : c.getPostalCode());
            json.put("status", c.getStatus());
            json.put("received", c.getReceived());
        }
        return json;
    }

    /**
     * 后台领奖记录分页
     *
     * @param clubName
     * @param levelName
     * @param createAtStart
     * @param createAtEnd
     * @param pageNum
     * @param pageSize
     * @param orderField
     * @param orderDirection
     * @return
     */
    public Pager<ClubAwardRecord> pager(String clubName, int levelId,
            Date createAtStart, Date createAtEnd, int pageNum, int pageSize,
            String orderField, String orderDirection) {

        SqlBuilder sql = new SqlBuilder();
        sql.appendSql(" SELECT ac_club_award_record.club_award_record_id FROM ac_club_award_record ,ac_club,ac_club_level ");
        sql.appendSql(" where ac_club_award_record.club_id = ac_club.club_id AND ac_club_award_record.level_id = ac_club_level.level_id ");
        if (StringUtils.isNotBlank(clubName)) {
            sql.appendSql(" and ac_club.name =").appendValue(clubName);
        }
        if (levelId > 0) {
            sql.appendSql(" and ac_club_level.level_id=").appendValue(levelId);
        }
        if (createAtStart != null) {
            sql.appendSql(" and ac_club_award_record.update_time>=").appendValue(createAtStart);
        }
        if (createAtEnd != null) {
            sql.appendSql(" and ac_club_award_record.update_time<=").appendValue(createAtEnd);
        }
        if (StringUtils.isNotBlank(orderField) && StringUtils.isNotBlank(orderDirection)) {
            sql.appendSql(" order by ac_club_award_record.").appendSql(orderField).appendSql(" ").appendSql(orderDirection);
        }
        return pagerService.pager(ClubAwardRecord.class, sql, pageNum, pageSize);
    }

    /**
     * 后台领奖记录分页
     *
     * @param acceptTimeStart
     * @param acceptTimeEnd
     * @param startLevel 升级前的等级ID
     * @param maxLevelOrder
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Pager<ClubUpgradeRecord> pager(String brand, String series, int theme, String area, Date acceptTimeStart, Date acceptTimeEnd, int startLevel, boolean maxLevelOrder, int pageNo, int pageSize) {
        /*
         SELECT a.club_id,b.name 车友会,a.start_level 初始星级, b.level_id 星级,b.rank 排名,SUBSTR(b.accept_date,1,10) 升级日期,b.club_url 个性域名 FROM (
         SELECT club_id,CASE WHEN MIN(level_id)=2 THEN 1 WHEN MIN(level_id)=3 THEN 2 
         WHEN MIN(level_id)=4 THEN 3 WHEN MIN(level_id)=5 THEN 4 ELSE 0 END start_level,MAX(level_id) max_level
         FROM ac_club_award_record WHERE accept_date > '2014-09-01' and accept_date < '2014-09-29' GROUP BY club_id
         ) a,(
         SELECT a.club_id,a.name ,b.level_id,a.rank,b.accept_date,a.club_url FROM ac_club a, ac_club_award_record b 
         WHERE a.club_id = b.club_id and b.accept_date > '2014-09-01' and b.accept_date < '2014-09-29'
         )b WHERE a.club_id = b.club_id AND b.rank > 0 AND a.max_level = b.level_id ORDER BY b.level_id DESC, b.rank;
         */
        SqlBuilder sql = new SqlBuilder();
        
        String select = ("select a.club_id clubId,b.name clubName,a.start_level-1 startLevel,b.level_id maxLevel,b.rank rank,b.accept_date acceptDate,b.club_url clubUrl,"
                + " b.brand_name brand, b.series_name series, b.province_name province, b.city_name city, b.theme_id themeId");
        sql.appendSql(select);
        sql.appendSql(" from ");
        sql.appendSql(" (select club_id,min(level_id) start_level,max(level_id) max_level ");
        sql.appendSql("  from ac_club_award_record ");
        sql.appendSql("  where accept_date > ").appendValue(acceptTimeStart).appendSql(" and accept_date < ").appendValue(acceptTimeEnd);
        sql.appendSql("  group by club_id) a, ");
        sql.appendSql(" (select a.club_id,a.name ,b.level_id,a.rank,b.accept_date,a.club_url,a.brand_name, a.series_name, a.province_name, a.city_name, a.theme_id ");
        sql.appendSql("  from ac_club a, ac_club_award_record b ");
        sql.appendSql("  where a.club_id = b.club_id ");
        if (StringUtils.isNotBlank(area)) {
            sql.appendSql(" and (a.province_name like ").appendValue("%" + area + "%");
            sql.appendSql(" or a.city_name like ").appendValue("%" + area + "%").appendSql(")");
        }
        if (StringUtils.isNotBlank(brand)) {
            sql.appendSql(" and a.brand_name like ").appendValue("%" + brand + "%");
        }
        if (StringUtils.isNotBlank(series)) {
            sql.appendSql(" and a.series_name like ").appendValue("%" + series + "%");
        }
        if (theme != 0) {
            sql.appendSql(" and a.theme_id= ").appendValue(theme);
        }
        sql.appendSql(" and b.accept_date > ").appendValue(acceptTimeStart).appendSql(" and b.accept_date < ").appendValue(acceptTimeEnd);
        sql.appendSql("  )b ");
        sql.appendSql(" where a.club_id = b.club_id and b.rank > 0 and a.max_level = b.level_id ");
        if (startLevel >= 0) {
            //a.start_level是升级后的id 但传入的startLevel是升级前的ID，所以要+1
            sql.appendSql(" and a.start_level = ").appendValue(startLevel + 1);
        }
        String order = maxLevelOrder ? "desc" : "asc";
        sql.appendSql(" order by b.level_id " + order + ", b.rank");
        int total = jdbcTemplate.queryForInt(sql.getSql().replace(select, "select count(1)"), sql.getValues());
        //分页
        Pager<ClubUpgradeRecord> pager = new Pager<ClubUpgradeRecord>();
        pager.setPageNo(pageNo);
        pager.setPageSize(pageSize);
        pager.setTotal(total);
        if (total == 0 || pageNo > pager.getPageCount() || (pageNo - 1) * pageSize < 0) {
            pager.setResultList(Collections.EMPTY_LIST);
            return pager;
        }
        sql.appendSql(" limit ").appendValue((pageNo - 1) * pageSize).appendSql(",").appendValue(pageSize);
        List<ClubUpgradeRecord> list = jdbcTemplate.query(sql.getSql(), sql.getValues(), new ResultSetExtractor<List<ClubUpgradeRecord>>() {
            @Override
            public List<ClubUpgradeRecord> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<ClubUpgradeRecord> list = new ArrayList<ClubUpgradeRecord>();
                while (rs.next()) {
                    ClubUpgradeRecord r = new ClubUpgradeRecord();
                    r.setAcceptDate(rs.getDate("acceptDate"));
                    r.setClubId(rs.getLong("clubId"));
                    r.setClubUrl(rs.getString("clubUrl"));
                    r.setMaxLevel(rs.getInt("maxLevel"));
                    r.setRank(rs.getInt("rank"));
                    r.setStartLevel(rs.getInt("startLevel"));
                    r.setClubName(rs.getString("clubName"));
                    r.setBrand(rs.getString("brand"));
                    r.setSeries(rs.getString("series"));
                    r.setProvince(rs.getString("province"));
                    r.setCity(rs.getString("city"));
                    r.setThemeId(rs.getLong("themeId"));
                    list.add(r);
                }
                return list;
            }
        });
        for(ClubUpgradeRecord cur:list){
            if(cur.getThemeId()>0){
                cur.setTheme(themeService.findThemeById(cur.getThemeId()).getThemeName());
            }
        }
        pager.setResultList(list);
        return pager;
    }
//	/**
//	 * 最新升级车友会
//	 * @return
//	 */
//	public ClubAwardRecord getNewRecored(){
//		SqlBuilder sql = new SqlBuilder();
//		sql.appendSql(" select * from ").appendSql(geliOrm.getTableName(ClubAwardRecord.class));
//		sql.appendSql(" order by accept_date desc");
//		return geliDao.findFirst(ClubAwardRecord.class, sql.getSql(), sql.getValues());
//	}
}